--USE Liberty
--GO
---------------------------------------------------------------------------------------------------
IF (SELECT OBJECT_ID('dbo.SaveServiceMan')) IS NOT NULL
BEGIN
	DROP PROCEDURE dbo.SaveServiceMan 
END
---------------------------------------------------------------------------------------------------

GO
CREATE PROCEDURE dbo.SaveServiceMan
@ServiceManOID			bigint			= NULL,
@ServiceManVersion		rowversion		= NULL,
@PersonVersion			rowversion		= NULL,
@FirstName				varchar(128)	= NULL,
@LastName				varchar(128)	= NULL,
@MiddleInitial			char(01)		= NULL,
@DOB					smalldatetime	= NULL,
@Sex					char(01)		= NULL,
@Phone1					varchar(20)		= NULL,
@Phone2					varchar(20)		= NULL,
@eMailAddress			varchar(128)	= NULL,
@PrimaryAddressOID		bigint			= NULL,
@PermanentAddressOID	bigint			= NULL,
@ServiceHistoryOID		bigint			= NULL,
@ServiceHistoryVersion	rowversion		= NULL,
@BranchOfServiceOID		bigint			= NULL,
@RankOID				bigint			= NULL,
@UnitAssignment			varchar(200)	= NULL,
@StartDate				datetime		= NULL,
@EndDate				datetime		= NULL,
@TypeOfDischargeOID		bigint			= NULL,
@Active					tinyint			= 1

AS 

---------------------------------------------------------------------------------------------------
--						Liberty
---------------------------------------------------------------------------------------------------
--  Stored Procedure :: SaveServiceMan
--
--	What it will and won't do...
--	Addresses must be saved prior to calling SaveServiceMan; pass in the ID's
--	Will insert/update the Person using SavePerson
--	Will insert/update the ServiceMan
--	Will insert/update ServiceHistory (this should probably be moved to a SaveServiceHistory proc)
---------------------------------------------------------------------------------------------------

SET NOCOUNT ON 

DECLARE @newOID bigint

CREATE TABLE #PersonCols
(oid		bigint
,version	varbinary(08)
)


---------------------------------------------------------------------------------------------------
-- Step 1: Call SavePerson, since a ServiceMan is really a person.
---------------------------------------------------------------------------------------------------
INSERT INTO #PersonCols (oid, version)
EXECUTE dbo.SavePerson	@oid			= @ServiceManOID,
						@version		= @PersonVersion,
						@FirstName		= @FirstName,
						@LastName		= @LastName,
						@MiddleInitial	= @MiddleInitial,
						@DOB			= @DOB,
						@Sex			= @Sex,
						@Phone1			= @Phone1,
						@Phone2			= @Phone2,
						@eMailAddress	= @eMailAddress
IF @@error <> 0
BEGIN
--	RAISERROR(99999, 16, 1, 'ServiceMan' )
	GOTO ExitError
END


---------------------------------------------------------------------------------------------------
-- Step 2: Ensure the Primary and Permanent addresses exist
---------------------------------------------------------------------------------------------------

IF NOT EXISTS(SELECT 1 FROM dbo.Addresses WHERE OID = @PrimaryAddressOID)
BEGIN
	RAISERROR('Primary address does not exist', 16, 1)
	GOTO ExitError
END


IF NOT EXISTS(SELECT 1 FROM dbo.Addresses WHERE OID = @PermanentAddressOID)
BEGIN
	RAISERROR('Permanent address does not exist', 16, 1)
	GOTO ExitError
END


---------------------------------------------------------------------------------------------------
-- Step 3: "Save" the ServiceMan
---------------------------------------------------------------------------------------------------
-- If no Serviceman OID was passed in, this is a new Person and ServiceMan; get the Person's
-- OID returned from the SavePerson call and perform an insert.
IF ISNULL(@ServiceManOID, 0) <= 0
BEGIN
	SELECT @ServiceManOID = oid
	FROM #PersonCols

	INSERT INTO dbo.ServiceMen
	(OID, PrimaryAddressID, PermanentAddressID, UpdatedDate, CreatedDate)
	VALUES
	(@ServiceManOID, @PrimaryAddressOID, @PermanentAddressOID, GETDATE(), GETDATE())

	-- 1.3. Retutn OID and timestamp of newly inserted object
	SELECT @ServiceManVersion = Version 
	FROM dbo.ServiceMen 
	WHERE OID = @ServiceManOID
END

-- If ServiceMan OID was passed in, perform an update
ELSE
BEGIN
	-- Attempt updating object (using both OID and timestamp for concurrency reasons)
	UPDATE dbo.ServiceMen
	SET PrimaryAddressID = @PrimaryAddressOID
		,PermanentAddressID = @PermanentAddressOID
		,UpdatedDate = GETDATE()
	WHERE (OID = @ServiceManOID) 
	AND (Version = @ServiceManVersion)

	-- If no records were found (and updated) - it means that another process had updated 
	-- this record (therefore timestamp will be different) or record was deleted (OID not found)
	-- Either way, we need to raise exception
	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR(99999, 16, 1, 'ServiceMan')
		GOTO ExitError
	END

	-- Record was found - return OID and new timestamp
	ELSE
	BEGIN 
		SELECT @ServiceManVersion = Version 
		FROM dbo.ServiceMen 
		WHERE OID = @ServiceManOID
	END
END


---------------------------------------------------------------------------------------------------
-- Step 4: "Save" the ServiceHistory
---------------------------------------------------------------------------------------------------
-- Right now, there is no discharge information on the screen, so get it if it isn't passed in
IF ISNULL(@TypeOfDischargeOID, 0) <= 0
BEGIN
	SELECT @TypeOfDischargeOID = OID
	FROM dbo.TypesOfDischarge
	WHERE Description = 'N/A'
END


IF ISNULL(@ServiceHistoryOID, 0) <= 0 
BEGIN
	-- If no ServiceHistory OID was passed in, this is a new entry for that ServiceMan.
	INSERT INTO dbo.ServiceHistory
	(PersonnelID, BranchOfServiceID, RankID, StartDate, EndDate, 
	UnitAssignment, TypeOfDischargeID, Active, UpdatedDate, CreatedDate)
	VALUES(@ServiceManOID, @BranchOfServiceOID, @RankOID, ISNULL(@StartDate, GETDATE()), @EndDate, 
	@UnitAssignment, @TypeOfDischargeOID, ISNULL(@Active, 1), GETDATE(), GETDATE())

	SET @ServiceHistoryOID =  SCOPE_IDENTITY()

	-- Set the OID and timestamp of newly inserted object
	SELECT @ServiceHistoryVersion = Version 
	FROM dbo.ServiceHistory 
	WHERE OID = @ServiceHistoryOID
END
ELSE
BEGIN
	-- ServiceHistory OID was supplied, so we will try to update the existing one
	-- Attempt updating object (using both OID and timestamp for concurrency reasons)
	UPDATE dbo.ServiceHistory
	SET BranchOfServiceID = @BranchOfServiceOID
		,RankID = @RankOID
		,StartDate = ISNULL(@StartDate, GETDATE())
		,EndDate = @EndDate
		,UnitAssignment = @UnitAssignment
		,TypeOfDischargeID = ISNULL(TypeOfDischargeID, 1)
		,Active = ISNULL(@Active, 1)
		,UpdatedDate = GETDATE()
	WHERE (OID = @ServiceHistoryOID) 
	AND (Version = @ServiceHistoryVersion)

	-- If no records were found (and updated) - it means that another process had updated 
	-- this record (therefore timestamp will be different) or record was deleted (OID not found)
	-- Either way, we need to raise exception
	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR(99999, 16, 1, 'ServiceHistory')
	END

	-- Record was found - return OID and new timestamp
	ELSE
	BEGIN 
		SELECT @ServiceHistoryVersion = Version 
		FROM dbo.ServiceHistory 
		WHERE OID = @ServiceHistoryOID
	END
END


---------------------------------------------------------------------------------------------------
-- Step 5: Return all the associated values to the caller.
---------------------------------------------------------------------------------------------------

SELECT sm.OID as 'ServiceManOID', sm.Version as 'ServiceManVersion', 
		p.Version as 'PersonVersion', 
		sh.OID as 'ServiceHistoryOID', sh.Version as 'ServiceHistoryVersion'
FROM dbo.ServiceMen sm
INNER JOIN dbo.People p
ON sm.OID = p.OID
INNER JOIN dbo.ServiceHistory sh
ON sm.OID = sh.PersonnelID
WHERE sm.OID = @ServiceManOID
AND sh.Version = (SELECT MAX(Version) FROM dbo.ServiceHistory WHERE PersonnelID = sm.OID)


--SELECT @ServiceManOID as 'ServiceManOID', 
--		@ServiceManVersion as 'ServiceManVersion', 
--		@PersonVersion as 'PersonVersion', 
--		@ServiceHistoryOID as 'ServiceHistoryOID', 
--		@ServiceHistoryVersion as 'ServiceHistoryVersion'

RETURN(0)


ExitError:
	RETURN(1)

GO

---------------------------------------------------------------------------------------------------
--	Grant execution rights
---------------------------------------------------------------------------------------------------
GRANT EXECUTE ON DBO.SaveServiceMan TO [Liberty_Users]
-- create an 
GO
